const mysql = require('mysql2/promise');
const config = require('../config');

// 创建数据库连接池
const pool = mysql.createPool({
  host: config.db.host,
  user: config.db.user,
  password: config.db.password,
  database: config.db.database,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// 存储当前事务连接
let transactionConnection = null;

// 执行SQL查询
exports.query = async (sql, params) => {
  // 如果有活动的事务连接，使用它
  if (transactionConnection) {
    try {
      const [rows] = await transactionConnection.query(sql, params);
      return rows;
    } catch (error) {
      console.error('事务中的数据库查询错误:', error);
      throw error;
    }
  } else {
    // 否则从连接池获取新连接
    const connection = await pool.getConnection();
    try {
      const [rows] = await connection.query(sql, params);
      return rows;
    } catch (error) {
      console.error('数据库查询错误:', error);
      throw error;
    } finally {
      connection.release();
    }
  }
};

// 开始事务
exports.beginTransaction = async () => {
  if (transactionConnection) {
    throw new Error('已有活动的事务');
  }
  
  transactionConnection = await pool.getConnection();
  await transactionConnection.beginTransaction();
};

// 提交事务
exports.commit = async () => {
  if (!transactionConnection) {
    throw new Error('没有活动的事务');
  }
  
  try {
    await transactionConnection.commit();
  } finally {
    transactionConnection.release();
    transactionConnection = null;
  }
};

// 回滚事务
exports.rollback = async () => {
  if (!transactionConnection) {
    throw new Error('没有活动的事务');
  }
  
  try {
    await transactionConnection.rollback();
  } finally {
    transactionConnection.release();
    transactionConnection = null;
  }
};

// 执行事务（回调方式）
exports.transaction = async (callback) => {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();
    const result = await callback(connection);
    await connection.commit();
    return result;
  } catch (error) {
    await connection.rollback();
    console.error('数据库事务错误:', error);
    throw error;
  } finally {
    connection.release();
  }
};
